public static string GetPSTransactionID()
{
lock (syncObject)
{
var dt = SqlDbService.GetDBRecords("select isnull(TransactionNo,0) from [dbo].[PSProfile]", DataSource.CSCMaster);
int TransID = (int)dt.Rows[0][0];
if (TransID == 0 || TransID == int.MaxValue)
{
TransID = 1;
}
else
{
TransID += 1;
}
SqlDbService.ExecuteCmd("update [dbo].[PSProfile] set TransactionNo=" + TransID, DataSource.CSCMaster);
return(string.Format("{0:0000000000}", TransID));
}
}
private void PopulateStock(DataTable dt)
{
if (dt.Rows.Count == 0)
{
return;
}
string sTableName;
SqlDataAdapter oDA;
DataRow tdr = null;
DateTime oDatetime = DateTime.Now;
using (SqlConnection oCon = new SqlConnection(GetConnectionString(DataSource.CSCMaster)))
{
DataTable dtSchema_STOCKMST = SqlDbService.GetTableSchema(oCon.ConnectionString, "CSCMaster.dbo.STOCKMST");
DataTable oDT_STOCKMST = SqlDbService.GetTableTemplate("CSCMaster.dbo.STOCKMST", oCon.ConnectionString);
DataTable dtSchema_StockPrice = SqlDbService.GetTableSchema(oCon.ConnectionString, "CSCMaster.dbo.Stock_Prices");
DataTable oDT_StockPrice = SqlDbService.GetTableTemplate("CSCMaster.dbo.Stock_Prices", oCon.ConnectionString);
DataTable dtSchema_STOCK_BR = SqlDbService.GetTableSchema(oCon.ConnectionString, "CSCMaster.dbo.STOCK_BR");
DataTable oDT_STOCK_BR = SqlDbService.GetTableTemplate("CSCMaster.dbo.STOCK_BR", oCon.ConnectionString);
DataTable dtSchema_PLUMAST = SqlDbService.GetTableSchema(oCon.ConnectionString, "CSCMaster.dbo.PLUMAST");
DataTable oDT_PLUMAST = SqlDbService.GetTableTemplate("CSCMaster.dbo.PLUMAST", oCon.ConnectionString);
oCon.Open();
SqlTransaction trans = oCon.BeginTransaction();
try
{
//CSCMaster.dbo.STOCKMST
sTableName = "CSCMaster.dbo.STOCKMST";
oDA = SqlDbService.CreateInsertSqlDataAdapter(sTableName, oCon, dtSchema_STOCKMST);
oDA.InsertCommand.Transaction = trans;
//dbSchema = SqlDbService.GetTableSchema(oCon.ConnectionString, sTableName);
//oDT = SqlDbService.GetTableTemplate(sTableName, oCon.ConnectionString);
foreach (DataRow sdr in dt.Rows)
{
tdr = oDT_STOCKMST.NewRow();
SqlDbService.InitializeRow(tdr, dtSchema_STOCKMST);
tdr["STOCK_CODE"] = sdr["UpcNumber"];
tdr["DESCRIPT"] = sdr["Name"];
tdr["STOCK_TYPE"] = "V";
tdr["Vendor"] = "ALL";
tdr["DEPT"] = "40";
tdr["PR_TYPE"] = "R";
tdr["PR_UNITS"] = "$";
tdr["Availability"] = 1;
tdr["CreationDate"] = oDatetime;
tdr["UpdateDate"] = oDatetime;
tdr["Packing"] = 1;
tdr["ElgLoyalty"] = 1;
tdr["PROD_DISC"] = 0;
tdr["STD_COST"] = 0;
tdr["AVG_COST"] = 0;
tdr["AVG_UNITS"] = 0;
tdr["LATE_COST"] = 0;
tdr["SUB_DEPT"] = "";
tdr["Sub_Detail"] = "";
tdr["SERIAL"] = 0;
tdr["S_BY_WGHT"] = 0;
tdr["QualTaxRebate"] = 0;
tdr["ElgTaxRebate"] = 0;
tdr["ElgTaxExemption"] = 0;
oDT_STOCKMST.Rows.Add(tdr);
}
oDA.Update(oDT_STOCKMST);
//CSCMaster.dbo.Stock_Prices
sTableName = "CSCMaster.dbo.Stock_Prices";
oDA = SqlDbService.CreateInsertSqlDataAdapter(sTableName, trans.Connection, dtSchema_StockPrice);
oDA.InsertCommand.Transaction = trans;
foreach (DataRow sdr in dt.Rows)
{
tdr = oDT_StockPrice.NewRow();
SqlDbService.InitializeRow(tdr, dtSchema_StockPrice);
tdr["Stock_Code"] = sdr["UpcNumber"];
tdr["Price_Number"] = 1;
tdr["Price"] = double.Parse(sdr["Amount"].ToString());
tdr["VendorID"] = "ALL";
tdr["OrderNo"] = 0;
tdr["Margin"] = 0;
tdr["UpdateDate"] = oDatetime;
oDT_StockPrice.Rows.Add(tdr);
}
oDA.Update(oDT_StockPrice);
//CSCMaster.dbo.STOCK_BR
sTableName = "CSCMaster.dbo.STOCK_BR";
oDA = SqlDbService.CreateInsertSqlDataAdapter(sTableName, trans.Connection, dtSchema_STOCK_BR);
oDA.InsertCommand.Transaction = trans;
foreach (DataRow sdr in dt.Rows)
{
tdr = oDT_STOCK_BR.NewRow();
SqlDbService.InitializeRow(tdr, dtSchema_STOCK_BR);
tdr["STOCK_CODE"] = sdr["UpcNumber"];
tdr["BRANCH"] = "01";
tdr["IN_STOCK"] = -7;
tdr["AVAIL"] = -7;
tdr["ONORDER_IC"] = 0;
tdr["ORDCOST_IC"] = 0;
tdr["ONORDER_PO"] = 0;
tdr["ALLOC_LAY"] = 0;
tdr["ALLOC_OE"] = 0;
tdr["RETURNED"] = 0;
tdr["HOLD"] = 0;
tdr["WASTE"] = 0;
tdr["STOCK_TAKE"] = 0;
tdr["TAKE_ENTER"] = 0;
tdr["MIN_QTY"] = 0;
tdr["MAX_QTY"] = 0;
tdr["REORD_QTY"] = 0;
tdr["LAST_SALE"] = DateTime.Now.ToShortDateString();
tdr["HOLD"] = 0;
oDT_STOCK_BR.Rows.Add(tdr);
}
oDA.Update(oDT_STOCK_BR);
//CSCMaster.dbo.Stock_Prices
sTableName = "CSCMaster.dbo.PLUMAST";
oDA = SqlDbService.CreateInsertSqlDataAdapter(sTableName, trans.Connection, dtSchema_PLUMAST);
oDA.InsertCommand.Transaction = trans;
foreach (DataRow sdr in dt.Rows)
{
tdr = oDT_PLUMAST.NewRow();
SqlDbService.InitializeRow(tdr, dtSchema_PLUMAST);
tdr["PLU_CODE"] = sdr["UpcNumber"];
tdr["PLU_PRIM"] = sdr["UpcNumber"];
tdr["PLU_TYPE"] = "S";
oDT_PLUMAST.Rows.Add(tdr);
}
oDA.Update(oDT_PLUMAST);
trans.Commit();
oDA.Dispose();
oCon.Close();
oCon.Dispose();
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
}
}