public void UpdateOver(DataTable pdtbData, DataTable pdtbOverData, ArrayList parrResultMasterID, StringBuilder psbResultMasterID, object pobjCycle)
{
PRO_DCPResultDetailDS dsDCPDetail = new PRO_DCPResultDetailDS();
PRO_DCOptionMasterVO voCycle = (PRO_DCOptionMasterVO)pobjCycle;
string strResultMasterIDs = "0";
#region check result master id in order to delete master data
// get the list of master id which will not be deleted
DataTable dtbNotDeleteID = dsDCPDetail.GetNotDeleteMasterID(psbResultMasterID.ToString(), voCycle.DCOptionMasterID);
foreach (DataRow drowData in dtbNotDeleteID.Rows)
{
parrResultMasterID.Remove(drowData[PRO_DCPResultMasterTable.DCPRESULTMASTERID_FLD].ToString());
}
foreach (string strMasterID in parrResultMasterID)
{
if (pdtbData.Select(PRO_DCPResultDetailTable.DCPRESULTMASTERID_FLD + "=" + strMasterID).Length == 0 &&
pdtbOverData.Select(PRO_DCPResultDetailTable.DCPRESULTMASTERID_FLD + "=" + strMasterID).Length == 0)
{
strResultMasterIDs += "," + strMasterID;
}
}
#endregion
StringBuilder sbOverID = new StringBuilder();
foreach (DataRow drowOver in pdtbOverData.Rows)
{
sbOverID.Append(drowOver[PRO_DCPResultDetailTable.DCPRESULTDETAILID_FLD].ToString()).Append(",");
}
sbOverID.Append("0"); // avoid exception
// delete over data first
dsDCPDetail.DeleteOverData(psbResultMasterID.ToString(), strResultMasterIDs, sbOverID.ToString());
// update quantity of over item
dsDCPDetail.UpdateOver(pdtbOverData);
// update new data to dcp table
DataSet dstData = new DataSet();
dstData.Tables.Add(pdtbData);
dsDCPDetail.UpdateDataSet(dstData);
}
/// <summary>
/// UpdateDataSetForDCP
/// </summary>
/// <param name="dstData"></param>
/// <param name="pstrMasterIDToUpdate"></param>
/// <param name="parrMasterIDToUpdate"></param>
/// <author>Trada</author>
/// <date>Monday, April 24 2006</date>
public void UpdateDataSetForDCP(DataSet dstData, string pstrMasterIDToUpdate, ArrayList parrMasterIDToUpdate, int pintDCOptionMasterID)
{
//Get ShiftPattern for refer information
PRO_ShiftPatternDS dsShiftPattern = new PRO_ShiftPatternDS();
DataSet dstShift = dsShiftPattern.List();
PRO_DCPResultDetailDS dsDCPResultDetail = new PRO_DCPResultDetailDS();
PRO_DCPResultMasterDS dsDCPResultMaster = new PRO_DCPResultMasterDS();
MTR_CPODS dsCPO = new MTR_CPODS();
//build a new dataset for new rows
#region Clone dataset contain new rows
DataSet dstNewDataSet = new DataSet();
DataSet dstModifiedDataSet = new DataSet();
dstNewDataSet = dstData.Clone();
dstModifiedDataSet = dstData.Clone();
foreach (DataRow drow in dstData.Tables[0].Rows)
{
if (drow.RowState == DataRowState.Added)
{
DataRow drowNew = dstNewDataSet.Tables[0].NewRow();
for (int i = 0; i < dstData.Tables[0].Columns.Count; i++)
{
drowNew[i] = drow[i];
}
dstNewDataSet.Tables[0].Rows.Add(drowNew);
}
if (drow.RowState == DataRowState.Modified)
{
DataRow drowNew = dstModifiedDataSet.Tables[0].NewRow();
for (int i = 0; i < dstData.Tables[0].Columns.Count; i++)
{
drowNew[i] = drow[i];
}
dstModifiedDataSet.Tables[0].Rows.Add(drowNew);
}
}
#endregion
//update data
#region sort new dataset by productid and start time
string strOrderby = ITM_ProductTable.PRODUCTID_FLD + "," + PRO_DCPResultDetailTable.STARTTIME_FLD;
DataRow[] adrowNewDataSet = dstNewDataSet.Tables[0].Select(string.Empty, strOrderby);
if (adrowNewDataSet.Length > 0)
{
#region Calculate and update Master Table
int i = 0;
while (i < adrowNewDataSet.Length)
{
int j = i;
decimal decQuantity = decimal.Parse(adrowNewDataSet[0][PRO_DCPResultDetailTable.QUANTITY_FLD].ToString());
DateTime dtmStartDateTime = new DateTime();
dtmStartDateTime = (DateTime)adrowNewDataSet[i][MTR_CPOTable.STARTDATE_FLD];
while ((j < adrowNewDataSet.Length - 1) && (int.Parse(adrowNewDataSet[j][ITM_ProductTable.PRODUCTID_FLD].ToString()) == int.Parse(adrowNewDataSet[j + 1][ITM_ProductTable.PRODUCTID_FLD].ToString())))
{
decQuantity += decimal.Parse(adrowNewDataSet[j + 1][PRO_DCPResultDetailTable.QUANTITY_FLD].ToString());
j = j + 1;
}
//assign value to VO
PRO_DCPResultMasterVO voDCPResultMaster = new PRO_DCPResultMasterVO();
//voDCPResultMaster.DCOptionMasterID = intDCOptionMasterID;
voDCPResultMaster.DCOptionMasterID = pintDCOptionMasterID;
voDCPResultMaster.Quantity = decQuantity;
voDCPResultMaster.StartDateTime = dtmStartDateTime;
voDCPResultMaster.ProductID = int.Parse(adrowNewDataSet[i][ITM_ProductTable.PRODUCTID_FLD].ToString());
voDCPResultMaster.DueDateTime = (DateTime)adrowNewDataSet[j][MTR_CPOTable.DUEDATE_FLD];
voDCPResultMaster.RoutingID = int.Parse(adrowNewDataSet[i][ITM_RoutingTable.ROUTINGID_FLD].ToString());
voDCPResultMaster.WorkCenterID = int.Parse(adrowNewDataSet[i][MST_WorkCenterTable.WORKCENTERID_FLD].ToString());
//insert to database
//insert to master table
int intDCPResultMasterID = dsDCPResultMaster.AddAndReturnID(voDCPResultMaster);
foreach (DataRow drowDetail in dstNewDataSet.Tables[0].Rows)
{
//set value of Converted column
drowDetail[PRO_DCPResultDetailTable.WOCONVERTED_FLD] = false;
//Set value of Start Time and End Time
drowDetail[PRO_DCPResultDetailTable.STARTTIME_FLD] = drowDetail[MTR_CPOTable.STARTDATE_FLD];
drowDetail[PRO_DCPResultDetailTable.ENDTIME_FLD] = drowDetail[MTR_CPOTable.DUEDATE_FLD];
//set value of Working date to detail table
DataRow[] adrowShift = dstShift.Tables[0].Select(PRO_ShiftPatternTable.SHIFTID_FLD + " = " + drowDetail[PRO_DCPResultDetailTable.SHIFTID_FLD].ToString());
if (adrowShift.Length > 0)
{
DateTime dtmWorkingDate = new DateTime();
dtmWorkingDate = GetDateOnly((DateTime)drowDetail[MTR_CPOTable.STARTDATE_FLD], (DateTime)adrowShift[0][PRO_ShiftPatternTable.WORKTIMEFROM_FLD], (DateTime)adrowShift[0][PRO_ShiftPatternTable.WORKTIMETO_FLD]);
drowDetail[PRO_DCPResultDetailTable.WORKINGDATE_FLD] = GetDateOnly(dtmWorkingDate);
}
//set value of totalsecond to detail table
//drowDetail[PRO_DCPResultDetailTable.TOTALSECOND_FLD] = GetTotalSecond(int.Parse(drowDetail[PRO_DCPResultDetailTable.SHIFTID_FLD].ToString()), dstShift, (DateTime)drowDetail[PRO_DCPResultDetailTable.STARTTIME_FLD], (DateTime)drowDetail[PRO_DCPResultDetailTable.ENDTIME_FLD]);
//set value of percentage to detail table
drowDetail[PRO_DCPResultDetailTable.PERCENTAGE_FLD] = 100 * Decimal.Parse(drowDetail[PRO_DCPResultDetailTable.QUANTITY_FLD].ToString()) / decQuantity;
//set value of MasterID to Detail Table
if (int.Parse(drowDetail[ITM_ProductTable.PRODUCTID_FLD].ToString()) == int.Parse(adrowNewDataSet[i][ITM_ProductTable.PRODUCTID_FLD].ToString()))
{
drowDetail[PRO_DCPResultMasterTable.DCPRESULTMASTERID_FLD] = intDCPResultMasterID;
}
}
i = j + 1;
}
#endregion
//update detail to database
dsDCPResultDetail.UpdateDataSetManual(dstNewDataSet);
}
#endregion
//Remove new rows
//delete row has status is add new
#region delete row has status is add new
DataRow[] adrowToDelete = dstData.Tables[0].Select(string.Empty, string.Empty, DataViewRowState.Added);
for (int i = 0; i < adrowToDelete.Length; i++)
{
adrowToDelete[i].Delete();
}
#endregion
foreach (DataRow drow in dstModifiedDataSet.Tables[0].Rows)
{
Int64 intDCPResultMasterToEdit = 0;
if (drow[PRO_DCPResultMasterTable.DCPRESULTMASTERID_FLD].ToString() != string.Empty)
{
intDCPResultMasterToEdit = Convert.ToInt64(drow[PRO_DCPResultDetailTable.DCPRESULTMASTERID_FLD]);
}
#region sort Modified dataset by productid and start time
string strOrderbyCond = PRO_DCPResultDetailTable.DCPRESULTMASTERID_FLD + "," + PRO_DCPResultDetailTable.STARTTIME_FLD;
DataRow[] adrowModifiedDataSet = dstData.Tables[0].Select(PRO_DCPResultDetailTable.DCPRESULTMASTERID_FLD + " = " + intDCPResultMasterToEdit.ToString(), strOrderbyCond);
if (adrowModifiedDataSet.Length > 0)
{
#region Calculate and update Master Table
int i = 0;
while (i < adrowModifiedDataSet.Length)
{
int j = i;
decimal decQuantity = decimal.Parse(adrowModifiedDataSet[0][PRO_DCPResultDetailTable.QUANTITY_FLD].ToString());
DateTime dtmStartDateTime = new DateTime();
dtmStartDateTime = (DateTime)adrowModifiedDataSet[i][MTR_CPOTable.STARTDATE_FLD];
while ((j < adrowModifiedDataSet.Length - 1) && (int.Parse(adrowModifiedDataSet[j][PRO_DCPResultDetailTable.DCPRESULTMASTERID_FLD].ToString()) == int.Parse(adrowModifiedDataSet[j + 1][PRO_DCPResultDetailTable.DCPRESULTMASTERID_FLD].ToString())))
{
decQuantity += decimal.Parse(adrowModifiedDataSet[j + 1][PRO_DCPResultDetailTable.QUANTITY_FLD].ToString());
j = j + 1;
}
//assign value to VO
PRO_DCPResultMasterVO voDCPResultMaster = new PRO_DCPResultMasterVO();
//voDCPResultMaster.DCOptionMasterID = intDCOptionMasterID;
//voDCPResultMaster.DCOptionMasterID = pintDCOptionMasterID;
voDCPResultMaster.DCPResultMasterID = int.Parse(adrowModifiedDataSet[i][PRO_DCPResultDetailTable.DCPRESULTMASTERID_FLD].ToString());
voDCPResultMaster.Quantity = decQuantity;
voDCPResultMaster.StartDateTime = dtmStartDateTime;
//voDCPResultMaster.ProductID = int.Parse(adrowNewDataSet[i][ITM_ProductTable.PRODUCTID_FLD].ToString());
voDCPResultMaster.DueDateTime = (DateTime)adrowModifiedDataSet[j][MTR_CPOTable.DUEDATE_FLD];
//insert to database
//insert to master table
dsDCPResultMaster.Update(voDCPResultMaster);
foreach (DataRow drowDetail in adrowModifiedDataSet)
{
//set value of Converted column
//drowDetail[PRO_DCPResultDetailTable.WOCONVERTED_FLD] = false;
//Set value of Start Time and End Time
drowDetail[PRO_DCPResultDetailTable.STARTTIME_FLD] = drowDetail[MTR_CPOTable.STARTDATE_FLD];
drowDetail[PRO_DCPResultDetailTable.ENDTIME_FLD] = drowDetail[MTR_CPOTable.DUEDATE_FLD];
//set value of Working date to detail table
DataRow[] adrowShift = dstShift.Tables[0].Select(PRO_ShiftPatternTable.SHIFTID_FLD + " = " + drowDetail[PRO_DCPResultDetailTable.SHIFTID_FLD].ToString());
if (adrowShift.Length > 0)
{
DateTime dtmWorkingDate = new DateTime();
dtmWorkingDate = GetDateOnly((DateTime)drowDetail[MTR_CPOTable.STARTDATE_FLD], (DateTime)adrowShift[0][PRO_ShiftPatternTable.WORKTIMEFROM_FLD], (DateTime)adrowShift[0][PRO_ShiftPatternTable.WORKTIMETO_FLD]);
drowDetail[PRO_DCPResultDetailTable.WORKINGDATE_FLD] = GetDateOnly(dtmWorkingDate);
}
//set value of totalsecond to detail table
//drowDetail[PRO_DCPResultDetailTable.TOTALSECOND_FLD] = GetTotalSecond(int.Parse(drowDetail[PRO_DCPResultDetailTable.SHIFTID_FLD].ToString()), dstShift, (DateTime)drowDetail[PRO_DCPResultDetailTable.STARTTIME_FLD], (DateTime)drowDetail[PRO_DCPResultDetailTable.ENDTIME_FLD]);
//set value of percentage to detail table
drowDetail[PRO_DCPResultDetailTable.PERCENTAGE_FLD] = 100 * Decimal.Parse(drowDetail[PRO_DCPResultDetailTable.QUANTITY_FLD].ToString()) / decQuantity;
}
i = j + 1;
}
#endregion
//update detail to database
dsDCPResultDetail.UpdateDataSetManual(dstData);
}
#endregion
}
#region Update DataSet (old code)
dsCPO.UpdateDataSetForDCP(dstData);
#endregion
#region Delete master Table for delete multi-rows event
//Update Master Table
//First, we get detail and master table from database for re-calculating master information
if (parrMasterIDToUpdate.Count > 0)
{
string strMasterIDToDelete = "0";
DataSet dstDCPResultDetailAfterSaving = new DataSet();
DataSet dstDCPResultMaster = new DataSet();
//Master Table
dstDCPResultMaster = dsDCPResultMaster.GetDCPResultMasterByArrayMasterID(pstrMasterIDToUpdate);
//Detail table
dstDCPResultDetailAfterSaving = dsDCPResultDetail.GetDCPResultDetailByMasterID(pstrMasterIDToUpdate);
if (dstDCPResultDetailAfterSaving.Tables[0].Rows.Count > 0)
{
if (parrMasterIDToUpdate.Count > 0)
{
for (int i = 0; i < parrMasterIDToUpdate.Count; i++)
{
//Select Detail for each masterID
DataRow[] adrowDetailByMasterID = dstDCPResultDetailAfterSaving.Tables[0].Select(PRO_DCPResultDetailTable.DCPRESULTMASTERID_FLD + " = " + parrMasterIDToUpdate[i].ToString(), PRO_DCPResultDetailTable.STARTTIME_FLD);
//Select Master for each masterID
DataRow[] adrowMasterByMasterID = dstDCPResultMaster.Tables[0].Select(PRO_DCPResultMasterTable.DCPRESULTMASTERID_FLD + " = " + parrMasterIDToUpdate[i].ToString());
if (adrowDetailByMasterID.Length > 0) //Update Master
{
if (adrowMasterByMasterID.Length > 0)
{
decimal decQuantityMaster = 0;
for (int j = 0; j < adrowDetailByMasterID.Length; j++)
{
if ((adrowDetailByMasterID[j][PRO_DCPResultDetailTable.QUANTITY_FLD] != null) &&
(adrowDetailByMasterID[j][PRO_DCPResultDetailTable.QUANTITY_FLD] != DBNull.Value))
{
decQuantityMaster += decimal.Parse(adrowDetailByMasterID[j][PRO_DCPResultDetailTable.QUANTITY_FLD].ToString());
}
}
adrowMasterByMasterID[0][PRO_DCPResultMasterTable.QUANTITY_FLD] = decQuantityMaster;
adrowMasterByMasterID[0][PRO_DCPResultMasterTable.STARTDATETIME_FLD] = (DateTime)adrowDetailByMasterID[0][PRO_DCPResultDetailTable.STARTTIME_FLD];
adrowMasterByMasterID[0][PRO_DCPResultMasterTable.DUEDATETIME_FLD] = (DateTime)adrowDetailByMasterID[adrowDetailByMasterID.Length - 1][PRO_DCPResultDetailTable.ENDTIME_FLD];
}
}
else
{
//Save MasterID to Delete
strMasterIDToDelete += "," + parrMasterIDToUpdate[i].ToString();
}
}
}
//Update Master
dsDCPResultMaster.UpdateDataSetDeleteMultiRows(dstDCPResultMaster);
//Delete Master
dsDCPResultMaster.DeleteMultiRows(strMasterIDToDelete);
}
else
{
//Delete Master
dsDCPResultMaster.DeleteMultiRows(pstrMasterIDToUpdate);
}
}
#endregion
}
public void ImportData(DataTable pdtbData, int pintCycleID, int pintWorkCenterID, int pintShiftID, DateTime pdtmMonth)
{
ImportPlanDataDS dsImport = new ImportPlanDataDS();
/// delete A1 table first
dsImport.Delete();
/// put data into A1 table
DataSet pdstData = new DataSet();
pdstData.Tables.Add(pdtbData);
dsImport.UpdateDataSet(pdstData);
/// shift pattern
PRO_ShiftPatternDS dsPattern = new PRO_ShiftPatternDS();
PRO_ShiftPatternVO voPattern = (PRO_ShiftPatternVO)dsPattern.GetObjectVOByShiftID(pintShiftID);
/// build the sql string
StringBuilder sbSQL = new StringBuilder();
for (int i = 1; i <= DateTime.DaysInMonth(pdtmMonth.Year, pdtmMonth.Month); i++)
{
string strFromDate = "'" + pdtmMonth.ToString("yyyy-MM") + "-" + i.ToString("00") + " " + voPattern.WorkTimeFrom.ToString("HH:mm:ss") + "'";
string strToDate = "'" + pdtmMonth.ToString("yyyy-MM") + "-" + i.ToString("00") + " " + voPattern.WorkTimeTo.ToString("HH:mm:ss") + "'";
DateTime dtmWorkingDate = new DateTime(pdtmMonth.Year, pdtmMonth.Month, i);
sbSQL.Append("SELECT " + strFromDate + " AS StartDate,");
sbSQL.Append(strToDate + " AS DueDate,");
sbSQL.Append(pintWorkCenterID + " AS WorkCenterID,");
sbSQL.Append("99999999.0002 AS TotalSecond,");
sbSQL.Append("99999999 AS DCPResultMasterID,");
sbSQL.Append(pintShiftID + " AS ShiftID,");
sbSQL.Append("1 AS IDNo,");
sbSQL.Append(pintCycleID + " AS DCOptionMasterID,");
sbSQL.Append("'" + dtmWorkingDate.ToString("yyyy-MM-dd HH:mm:ss") + "' AS WorkingDate,");
sbSQL.Append("ProductID, ISNULL(F" + i + ",0) AS Qty FROM A1");
sbSQL.Append("\n");
if (i < DateTime.DaysInMonth(pdtmMonth.Year, pdtmMonth.Month))
{
sbSQL.Append("UNION ALL").Append("\n");
}
}
/// put data into A2 from A1
string strSql = "IF EXISTS(select id from dbo.sysobjects where id = object_id(N'A2') and OBJECTPROPERTY(id, N'IsUserTable') = 1)\n"
+ "BEGIN\n"
+ " Drop Table A2\n"
+ "End\n"
+ "IF EXISTS(select id from dbo.sysobjects where id = object_id(N'A3') and OBJECTPROPERTY(id, N'IsUserTable') = 1)\n"
+ "Begin\n"
+ " Drop Table A3\n"
+ "End\n"
+ "SELECT * INTO A2 FROM\n"
+ "(" + sbSQL.ToString() + ") AS B1";
Debug.WriteLine(strSql);
dsImport.ExecuteCommand(strSql);
/// put data into A3 table
strSql = "SELECT StartDate, DueDate, WorkCenterID, TotalSecond, DCPResultMasterID,"
+ " ShiftID, DCOptionMasterID, WorkingDate, ProductID, Qty, IDENTITY(int,1,1) as IDNo"
+ " INTO A3 FROM A2"
+ " WHERE Qty > 0";
dsImport.ExecuteCommand(strSql);
/// delete DCP result detail
PRO_DCPResultDetailDS dsDCPResultDetail = new PRO_DCPResultDetailDS();
dsDCPResultDetail.Delete(pintCycleID, pintWorkCenterID, pintShiftID, pdtmMonth);
/// put data to dcp result table
strSql = "Update A3 \n"
+ " Set TotalSecond=Round(ISNULL(Qty*("
+ " select LTVariableTime from ITM_Product where productid=A3.productid),0),0)\n"
+ " Insert Into PRO_DCPResultMaster (DCOptionMasterID,MasterShiftID,WorkCenterID,StartDateTime,DueDateTime,Quantity,ProductID,DeliveryQuantity)\n"
+ " (Select DCOptionMasterID,ShiftID,WorkCenterID,\n"
+ " StartDate,DueDate,Qty,ProductID,IDNo\n"
+ " from A3 where Qty>0)\n"
+ " Update A3\n"
+ " Set DCPResultMasterID=(select DCPResultMasterID from PRO_DCPResultMaster where DCOptionMasterID=A3.DCOptionMasterID\n"
+ " and WorkCenterID=A3.WorkCenterID and ProductID=A3.ProductID AND DeliveryQuantity=A3.IDNo)\n"
+ " Insert Into PRO_DCPResultDetail (StartTime,EndTime,TotalSecond,Quantity,DCPResultMasterID,WorkingDate,ShiftID,Percentage)\n"
+ " (select StartDate,DueDate,TotalSecond,qty,DCPResultMasterID,WorkingDate,ShiftID,'100' as Percentage FROM A3"
+ " Where Qty>0)\n"
+ " Update PRO_DCPResultMaster\n"
+ " set DeliveryQuantity=0";
dsImport.ExecuteCommand(strSql);
}
/// <summary>
/// UpdateDataSetAfterSaving
/// </summary>
/// <param name="pdstData"></param>
/// <author>Trada</author>
/// <date>Saturday, June 10 2006</date>
public void UpdateDataSetAfterSaving(DataSet pdstData)
{
PRO_DCPResultDetailDS dsDCPResultDetail = new PRO_DCPResultDetailDS();
dsDCPResultDetail.UpdateDataSetManual(pdstData);
}
