using HotelPms.Share.Util;
|
using System;
|
using System.Text;
|
using System.Windows.Forms;
|
|
namespace HotelPms.SourceFactory.Util
|
{
|
public class CommonFunc
|
{
|
/// <summary>
|
/// csÌ^ËProto3Type
|
/// </summary>
|
/// <param name="type">csÌ^</param>
|
/// <returns></returns>
|
public static string ToProto3Type(string type)
|
{
|
if (type.ToLower() == "int") { return "int32"; }
|
else if (type.ToLower() == "bytestring") { return "bytes"; }
|
else if (type.ToLower() == "long") { return "int64"; }
|
else if (type.ToLower() == "datetime") { return "google.protobuf.Timestamp"; }
|
else if (type.ToLower() == "timestamp") { return "google.protobuf.Timestamp"; }
|
else if (type.ToLower() == "decimal") { return "customTypes.DecimalValue"; }
|
else if (type.ToLower() == "decimalvalue") { return "customTypes.DecimalValue"; }
|
else if (type.ToLower() == "date") { return "customTypes.Date"; }
|
else { return type.ToLower(); }
|
}
|
|
/// <summary>
|
/// ÏæèSQL¶Ìì¬
|
/// </summary>
|
/// <param name="type">CSÌ^</param>
|
/// <param name="name"></param>
|
/// <returns></returns>
|
public static string ToSqlValue(string type, string name)
|
{
|
if ("bool".Equals(type))
|
{
|
return "{(" + (name + " ? 1 : 0") + ")}";
|
}
|
else if ("decimal".Equals(type) || "int".Equals(type) || "float".Equals(type))
|
{
|
return "{" + name + "}";
|
}
|
else if ("DateTime".Equals(type))
|
{
|
return "{" + "CConvert.ToSqlValue(" + name + ")" + "}";
|
}
|
else if ("Timestamp".Equals(type))
|
{
|
return "{(" + name + " == null ? \"NULL\" : " + "CConvert.ToSqlValue(" + name + ".ToDateTime())" + ")}";
|
}
|
else if ("DecimalValue".Equals(type))
|
{
|
return "{(" + name + " == null ? \"0\" : " + name + ".ToSqlValue()" + ")}";
|
}
|
else if ("Date".Equals(type))
|
{
|
return "{(" + name + " == null ? \"NULL\" : " + name + ".ToSqlValue()" + ")}";
|
}
|
else
|
{
|
return "N'{" + name + "}'";
|
}
|
}
|
|
public static string ToInputChar(string sqlType)
|
{
|
switch (sqlType)
|
{
|
case "int":
|
case "tinyint":
|
case "smallint":
|
case "decimal":
|
case "bit":
|
return "EInputChar.Num";
|
case "float":
|
return "EInputChar.Num | EInputChar.Dot";
|
case "date":
|
case "smalldatetime":
|
case "datetime":
|
return "EInputChar.Num | EInputChar.Slash, InputStyle = EInputStyle.Date";
|
default:
|
return "EInputChar.None";
|
}
|
}
|
|
/// <summary>
|
/// DBÌ^CvËCS^Cv
|
/// </summary>
|
/// <param name="sqlType"></param>
|
/// <returns></returns>
|
public static string ConvertToCsType(string sqlType)
|
{
|
switch (sqlType)
|
{
|
case "int":
|
case "tinyint":
|
case "smallint":
|
return "int";
|
case "decimal":
|
return "DecimalValue";
|
case "bit":
|
return "bool";
|
case "date":
|
return "Date";
|
case "smalldatetime":
|
case "datetime":
|
return "Timestamp";
|
case "float":
|
return "float";
|
default:
|
return "string";
|
}
|
}
|
|
/// <summary>
|
/// SQL^CvÌúl
|
/// </summary>
|
/// <param name="sqlType"></param>
|
/// <returns></returns>
|
public static string GetDefValue(string sqlType)
|
{
|
switch (sqlType)
|
{
|
case "int":
|
case "tinyint":
|
case "smallint":
|
return "0";
|
case "date":
|
return "Date.Default";
|
case "decimal":
|
case "DecimalValue":
|
case "float":
|
return "0";
|
case "bit":
|
return "false";
|
case "smalldatetime":
|
case "datetime":
|
return "CConvert.ToTimestamp(DateTime.MinValue)";
|
default:
|
return "string.Empty";
|
}
|
}
|
|
/// <summary>
|
/// è`ì¬
|
/// </summary>
|
/// <param name="sqlType"></param>
|
/// <param name="varName"></param>
|
/// <returns></returns>
|
public static string GetDeclare(string sqlType,string varName)
|
{
|
return new string(' ', 8) + "private " + ConvertToCsType(sqlType) + " " + varName + " = " + GetDefValue(sqlType) + ";" + Environment.NewLine;
|
}
|
|
/// <summary>
|
/// DataGridViewÌÚËtxtÚ
|
/// </summary>
|
/// <param name="sqlType"></param>
|
/// <param name="context"></param>
|
/// <returns></returns>
|
public static string GetToTextFunction(string sqlType, string context)
|
{
|
switch (sqlType)
|
{
|
case "int":
|
case "tinyint":
|
case "smallint":
|
return string.Format("GeneralSub.ToFormText(CConvert.ToInt({0}))", context);
|
case "bit":
|
return string.Format("CConvert.ToBool({0}) ? \"1\" : \"0\"", context);
|
case "decimal":
|
return string.Format("CConvert.ToDecimal({0})", context);
|
case "float":
|
return string.Format("CConvert.ToFloat({0})", context);
|
case "smalldatetime":
|
case "datetime":
|
case "date":
|
return string.Format("CConvert.ToDateString({0})", context);
|
default:
|
return string.Format("CConvert.ToString({0})", context);
|
}
|
}
|
|
/// <summary>
|
/// ShowDetailp
|
/// DataGridViewÌÚËtxtÚ
|
/// </summary>
|
/// <param name="sqlType"></param>
|
/// <param name="field"></param>
|
/// <param name="usingEndKey"></param>
|
/// <returns></returns>
|
public static string GetDataRowToText(string sqlType, string field, bool usingEndKey)
|
{
|
StringBuilder text = new StringBuilder();
|
text.Append(new string(' ', 12));
|
|
string context = string.Format("GetCellValue(\"{0}\")", field);
|
text.AppendFormat("txt{0}.Text = {1};", field, GetToTextFunction(sqlType, context));
|
if (usingEndKey) { text.Append(Environment.NewLine); text.AppendFormat("SetDispName(txt{0});", field); }
|
text.Append(Environment.NewLine);
|
return text.ToString();
|
}
|
|
public static string GetChangeFunction(string sqlType)
|
{
|
switch (sqlType)
|
{
|
case "int":
|
case "tinyint":
|
case "smallint":
|
return "CConvert.ToInt";
|
case "decimal":
|
return "CConvert.ToDecimal";
|
case "bit":
|
return "CConvert.ToBool";
|
case "float":
|
return "CConvert.ToFloat";
|
case "smalldatetime":
|
case "datetime":
|
case "date":
|
return "CConvert.ToDateTime";
|
default:
|
return "CConvert.ToString";
|
}
|
}
|
|
/// <summary>
|
/// ToDataRowÌl
|
/// </summary>
|
/// <param name="type">csÌ^</param>
|
/// <param name="fieldName"></param>
|
/// <returns></returns>
|
public static string ToDataRow(string type, string name)
|
{
|
if ("Timestamp".Equals(type))
|
{
|
return $"{name}.ToDateTime()";
|
}
|
else if ("Date".Equals(type))
|
{
|
return $"{name}.ToDateTime()";
|
}
|
else if ("DecimalValue".Equals(type))
|
{
|
return $"{name}.ToDecimal()";
|
}
|
else
|
{
|
return name;
|
}
|
}
|
|
|
/// <summary>
|
/// SetFieldp
|
/// æÊü͵½¶ñÅNXÖXV·é
|
/// </summary>
|
/// <param name="type">csÌ^</param>
|
/// <param name="fieldName"></param>
|
/// <returns></returns>
|
public static string GetConvertValue(string type, string fieldName)
|
{
|
if ("bool".Equals(type))
|
{
|
return $"{fieldName} = CConvert.ToBool(value); ";
|
}
|
else if ("decimal".Equals(type) || "DecimalValue".Equals(type))
|
{
|
return $"{fieldName} = CConvert.ToDecimal(value); ";
|
}
|
else if ("int".Equals(type))
|
{
|
return $"{fieldName} = CConvert.ToInt(value); ";
|
}
|
else if ("float".Equals(type))
|
{
|
return $"{fieldName} = CConvert.ToFloat(value); ";
|
}
|
else if ("string".Equals(type))
|
{
|
return $"{fieldName} = value.ToString(); ";
|
}
|
else if ("DateTime".Equals(type))
|
{
|
return $"{fieldName} = CConvert.ToDateTime(value); ";
|
}
|
else if ("Timestamp".Equals(type))
|
{
|
return $"{fieldName} = CConvert.ToTimestamp(CConvert.ToDateTime(value)); ";
|
}
|
else if ("Date".Equals(type))
|
{
|
return $"{fieldName} = new Date(CConvert.ToDateInt(value)); ";
|
}
|
else
|
{
|
return $"{fieldName} = ({type})value; ";
|
}
|
}
|
|
/// <summary>
|
/// DataRow Ë csÌNXÖ
|
/// </summary>
|
/// <param name="type">csÌ^</param>
|
/// <param name="fieldName"></param>
|
/// <returns></returns>
|
public static string GetConvertDataRowCode(string type, string fieldName)
|
{
|
string ret = string.Empty;
|
if ("bool".Equals(type))
|
{
|
ret += string.Format("{0} = CConvert.ToBool(row[\"{0}\"]); ", fieldName) + Environment.NewLine;
|
}
|
else if ("decimal".Equals(type) || "DecimalValue".Equals(type))
|
{
|
ret += string.Format("{0} = CConvert.ToDecimal(row[\"{0}\"]); ", fieldName) + Environment.NewLine;
|
}
|
else if ("int".Equals(type))
|
{
|
ret += string.Format("{0} = CConvert.ToInt(row[\"{0}\"],{0}); ", fieldName) + Environment.NewLine;
|
}
|
else if ("float".Equals(type))
|
{
|
ret += string.Format("{0} = CConvert.ToFloat(row[\"{0}\"],{0}); ", fieldName) + Environment.NewLine;
|
}
|
else if ("string".Equals(type))
|
{
|
ret += string.Format("{0} = row[\"{0}\"].ToString(); ", fieldName) + Environment.NewLine;
|
}
|
else if ("DateTime".Equals(type))
|
{
|
ret += string.Format("{0} = row.IsNull(\"{0}\") ? DateTime.MinValue : (System.DateTime)row[\"{0}\"]; ", fieldName) + Environment.NewLine;
|
}
|
else if ("Date".Equals(type))
|
{
|
ret += string.Format("{0} = new Date(row.IsNull(\"{0}\") ? 0 : CConvert.ToDateInt((System.DateTime)row[\"{0}\"])); ", fieldName) + Environment.NewLine;
|
}
|
else if ("Timestamp".Equals(type))
|
{
|
ret += string.Format("{0} = CConvert.ToTimestamp(row.IsNull(\"{0}\") ? DateTime.MinValue : (System.DateTime)row[\"{0}\"]); ", fieldName) + Environment.NewLine;
|
}
|
else
|
{
|
ret += string.Format("{0} = ({1})ConvertDBValue(row[\"{0}\"],{0}); ", fieldName, type) + Environment.NewLine;
|
}
|
return " " + ret;
|
}
|
|
public static string GetAddSql(DataGridView dgvData, string tableName)
|
{
|
string ret = string.Empty;
|
string fieldList = string.Empty;
|
string indexList = string.Empty;
|
int i = 0;
|
foreach (DataGridViewRow row in dgvData.Rows)
|
{
|
string type = CommonFunc.ConvertToCsType(row.Cells["Type"].Value.ToString());
|
string name = row.Cells["Field"].Value.ToString();
|
|
if (name.Equals("UpdateDate"))
|
{
|
fieldList += ",UpdateDate";
|
indexList += ",GETDATE()";
|
}
|
else
|
{
|
if (i > 0) { fieldList += ","; indexList += ","; ret += ","; }
|
fieldList += name;
|
|
if ("bool".Equals(type))
|
{
|
indexList += ("{" + i.ToString() + "}");
|
ret += (name + " ? 1 : 0 ");
|
}
|
else if ("decimal".Equals(type) || "int".Equals(type) || "float".Equals(type))
|
{
|
indexList += ("{" + i.ToString() + "}");
|
ret += (name);
|
}
|
else if ("DateTime".Equals(type))
|
{
|
indexList += ("{" + i.ToString() + "}");
|
ret += ("CConvert.ToSqlValue(" + name + ".ToDateTime())");
|
}
|
else
|
{
|
indexList += ("N'{" + i.ToString() + "}'");
|
ret += (name);
|
}
|
i++;
|
}
|
}
|
|
return "return string.Format(@\"" + string.Format("INSERT INTO {0}(", tableName) + fieldList + ") VALUES(" + indexList + ");" + "\"," + ret + ");";
|
|
}
|
|
public static string GetUpdateSql(DataGridView dgvData, string tableName)
|
{
|
string ret = string.Empty;
|
string fieldList = string.Empty;
|
int fieldCount = 0;
|
string whereList = string.Empty;
|
int whereCount = 0;
|
int i = 0;
|
foreach (DataGridViewRow row in dgvData.Rows)
|
{
|
string type = CommonFunc.ConvertToCsType(row.Cells["Type"].Value.ToString());
|
string name = row.Cells["Field"].Value.ToString();
|
|
if (name.Equals("UpdateDate"))
|
{
|
fieldList += ",UpdateDate = GETDATE()";
|
}
|
else
|
{
|
if (i > 0) { ret += ","; }
|
|
//uÚ={index}vÌ®
|
string item = name + " = ";
|
|
if ("bool".Equals(type))
|
{
|
item += ("{" + i.ToString() + "}");
|
ret += (name + " ? 1 : 0 ");
|
}
|
else if ("decimal".Equals(type) || "int".Equals(type) || "float".Equals(type))
|
{
|
item += ("{" + i.ToString() + "}");
|
ret += name;
|
}
|
else if ("DateTime".Equals(type))
|
{
|
item += ("{" + i.ToString() + "}");
|
ret += "CConvert.ToSqlValue(" + name + ".ToDateTime())";
|
}
|
else
|
{
|
item += ("N'{" + i.ToString() + "}'");
|
ret += name;
|
}
|
|
|
if (CConvert.ToInt(row.Cells["PKFlg"].Value.ToString()) == 1)
|
{
|
if (whereCount > 0) { whereList += ","; }
|
whereList += item;
|
whereCount++;
|
}
|
else
|
{
|
if (fieldCount > 0) { fieldList += ","; }
|
fieldList += item;
|
fieldCount++;
|
}
|
i++;
|
}
|
}
|
|
return "return string.Format(@\"" + string.Format("UPDATE {0} SET ", tableName) + fieldList + " WHERE " + whereList + ";" + "\"," + ret + ");";
|
|
}
|
|
public static string GetConvertReaderCode(string sqlType, string fieldName)
|
{
|
string ret = string.Empty;
|
if ("bigint".Equals(sqlType))
|
{
|
ret = $"{fieldName} = (long)(row.IsDBNull(i) ? 0 : row.GetInt64 (i)); i++;";
|
}
|
else if ("binary".Equals(sqlType))
|
{
|
//ret = $"{fieldName} = (long)(row.IsDBNull(i) ? null : row.GetBytes(i)); i++;";
|
}
|
else if ("bit".Equals(sqlType))
|
{
|
ret = $"{fieldName} = (bool)(row.IsDBNull(i) ? false : row.GetBoolean(i)); i++;";
|
}
|
else if ("date".Equals(sqlType))
|
{
|
ret = $"{fieldName} = new Date(row.IsDBNull(i) ? 0 : CConvert.ToDateInt(row.GetDateTime(i))); i++;";
|
}
|
else if ("datetime".Equals(sqlType) || "datetime2".Equals(sqlType) || "smalldatetime".Equals(sqlType) || "time".Equals(sqlType))
|
{
|
ret = $"{fieldName} = CConvert.ToTimestamp(row.IsDBNull(i) ? DateTime.MinValue : row.GetDateTime(i)); i++;";
|
}
|
else if ("datetimeoffset".Equals(sqlType))
|
{
|
//ret = $"{fieldName} = (DateTimeOffset)(row.IsDBNull(i) ? 0 : row.GetBytes(i)); i++;";
|
}
|
else if ("decimal".Equals(sqlType) || "money".Equals(sqlType) || "numeric".Equals(sqlType) || "smallmoney".Equals(sqlType))
|
{
|
ret = $"{fieldName} = (decimal)(row.IsDBNull(i) ? 0M : row.GetDecimal(i)); i++;";
|
}
|
else if ("float".Equals(sqlType))
|
{
|
ret = $"{fieldName} = (float)(row.IsDBNull(i) ? 0D : row.GetDouble(i)); i++;";
|
}
|
else if ("int".Equals(sqlType))
|
{
|
ret = $"{fieldName} = (int)(row.IsDBNull(i) ? 0 : row.GetInt32(i)); i++;";
|
}
|
else if ("real".Equals(sqlType))
|
{
|
ret = $"{fieldName} = (float)(row.IsDBNull(i) ? 0F : row.GetFloat(i)); i++;";
|
}
|
else if ("smallint".Equals(sqlType))
|
{
|
ret = $"{fieldName} = (int)(row.IsDBNull(i) ? 0 : row.GetInt16(i)); i++;";
|
}
|
else if ("tinyint".Equals(sqlType))
|
{
|
ret = $"{fieldName} = (byte)(row.IsDBNull(i) ? 0x00 : row.GetByte(i)); i++;";
|
}
|
else
|
{
|
ret = $"{fieldName} = (row.IsDBNull(i) ? string.Empty : row.GetString(i)); i++;";
|
}
|
return " " + ret + Environment.NewLine;
|
}
|
}
|
}
|
