public string GenereteUpdateSql(int type = 0,int localdb=0)        {            StringBuilder sb = new StringBuilder();            StringBuilder sbToday = new StringBuilder();            sbToday.Append("");            if (localdb == 1) DbHelper.Conn = "Data Source=192.168.9.222;Initial Catalog=test;Persist Security Info=True;User ID=dev;Password=dev;";            else DbHelper.Conn = "Data Source=****;Initial Catalog=test;Persist Security Info=True;User ID=test;Password=test";            DbHelper db = new DbHelper();            string sql = "SELECT  MAX(ReceiptNo) ReceiptNo FROM dbo.FinanceReceipts  f  GROUP BY f.ReceiptNo  HAVING COUNT(f.ReceiptNo)>1";//WHERE SUBSTRING(f.ReceiptNo,3,2)!='LS' ,,COUNT(ReceiptNo) repeatcount,MAX(ObjectType) ObjectType,MAX(CreatedDate) CreatedDate,MAX(CASE WHEN  IsSummary=1 THEN 1 ELSE 0 END ) isSummary,            DataTable dtReceiptNo = db.ExecuteDataTable(sql);            Dictionary
     
       DateTypeSeriaNo = new Dictionary
      
       ();            if (dtReceiptNo != null && dtReceiptNo.Rows.Count > 0)            {                foreach (DataRow drReceiptNo in dtReceiptNo.Rows)                {                    string ReceiptNoQuery = drReceiptNo["ReceiptNo"].ToString();                    DataTable dtData = db.ExecuteDataTable(string.Format("select ReceiptId,ReceiptNo,ObjectType,CreatedDate,CASE WHEN  ReceiptStatus=0 THEN 0 ELSE 1 END IsCommit  FROM dbo.FinanceReceipts WHERE ReceiptNo='{0}' ", ReceiptNoQuery));                    if (dtData != null && dtData.Rows.Count > 1)//同一编号大于2个                    {                        for (int i = 0; i < dtData.Rows.Count - 1; i++)//更新前n-1个                        {                            DataRow drData = dtData.Rows[i];                            int ReceiptId = Convert.ToInt32(drData["ReceiptId"]);                            int ObjectType = Convert.ToInt32(drData["ObjectType"]);                            string CreatedDate = drData["CreatedDate"].ToString();                            int maxSerial = 0;                            int IsCommit = Convert.ToInt32(drData["IsCommit"]);                            string DateTypeKey = string.Format("{0}{1}{2}", CreatedDate, ObjectType, IsCommit);                            if (DateTypeSeriaNo.ContainsKey(DateTypeKey))                            {                                maxSerial = DateTypeSeriaNo[DateTypeKey];                            }                            {                                string maxSerialSql = "";                                if (IsCommit == 0) maxSerialSql = string.Format(" SELECT MAX(SerialNumber) FROM dbo.FinanceReceipts WHERE  ObjectType={0} AND ReceiptStatus=0 AND CreatedDate='{1}' ", ObjectType, CreatedDate);                                else maxSerialSql = string.Format(" SELECT MAX(SerialNumber) FROM dbo.FinanceReceipts WHERE  ObjectType={0} AND ReceiptStatus!=0 AND CreatedDate='{1}' ", ObjectType, CreatedDate);                                object retobj = db.ExecuteScalar(maxSerialSql);                                int dbMaxSerail = 0;                                if (retobj != DBNull.Value) dbMaxSerail = Convert.ToInt32(retobj);                                maxSerial=(maxSerial>dbMaxSerail?maxSerial:dbMaxSerail);                                DateTypeSeriaNo[DateTypeKey] = maxSerial;//存入dict                            }                            string ReceiptNo = string.Empty;                            switch (ObjectType)                            {                                case 1: // 应收                                    ReceiptNo = "YS";                                    break;                                case 2: // 收款                                    ReceiptNo = "SK";                                    break;                                case 3: // 应付                                    ReceiptNo = "YF";                                    break;                                case 4: // 付款                                    ReceiptNo = "FK";                                    break;                                default:                                    throw new InvalidOperationException("未知票据类型，不能生成单据编号");                            }                            // 未提交                            if (IsCommit == 0 || ReceiptNoQuery.Substring(2, 2) == "LS")                            {                                ReceiptNo += "LS";                            }                            ReceiptNo += CreatedDate;                            maxSerial++;                            DateTypeSeriaNo[DateTypeKey] = maxSerial;                            ReceiptNo += string.Format("{0:D6}", maxSerial);                            string CreatedDateToday=DateTime.Now.ToString("yyyyMMdd");                            if (CreatedDate == CreatedDateToday)                            {                                sbToday.AppendFormat("UPDATE dbo.FinanceReceipts SET SerialNumber={0},ReceiptNo='{1}' WHERE ReceiptId={2};
       
", maxSerial, ReceiptNo, ReceiptId);                                sbToday.AppendFormat("INSERT INTO dbo.FinanceBillLog(ReceiptId,[Action] ,Remark ,CreatedById ,CreatedByName ,CreatedDate) VALUES  ({0},N'修改重复付款单编号' ,N'从 {1} 改为 {2}' ,0 ,N'sql' ,'2015-05-22 15:30:00');
       
", ReceiptId, ReceiptNoQuery, ReceiptNo);                            }                            else                            {                                sb.AppendFormat("UPDATE dbo.FinanceReceipts SET SerialNumber={0},ReceiptNo='{1}' WHERE ReceiptId={2};
       
", maxSerial, ReceiptNo, ReceiptId);                                sb.AppendFormat("INSERT INTO dbo.FinanceBillLog(ReceiptId,[Action] ,Remark ,CreatedById ,CreatedByName ,CreatedDate) VALUES  ({0},N'修改重复付款单编号' ,N'从 {1} 改为 {2}' ,0 ,N'sql' ,'2015-05-22 15:30:00');
       
", ReceiptId, ReceiptNoQuery, ReceiptNo);                            }                        }                    }                }            }            sbToday.Append("
      
     ");            if (type == 1) return sb.ToString();            if (type == 2) return sbToday.ToString();            return sb.ToString() + "
" + sbToday.ToString();        }