private DataTable GetActualStock()
{
try
{
SysConfig sysConfig = new SysConfig();
string StockStatisticlastTime = sysConfig.GetLastTime("StockStatisticLastTime");//最后更新时间
DateTime CreateDate = Convert.ToDateTime(StockStatisticlastTime);
long LastUpLoadStockDate = Common.LocalDateTimeToUtcLong(CreateDate);
DataTable StockTable = DBHelper.GetTable("账面库存统计查询", GlobalStaticObj_Server.DbPrefix + GlobalStaticObj_Server.Instance.MainAccCode,
"v_upload_ytstock", "car_parts_code,statis_count", "statistic_date>" + LastUpLoadStockDate, "", "");
return StockTable;
}
catch (Exception ex)
{
GlobalStaticObj_Server.GlobalLogService.WriteLog("UCHomePage", ex);
return null;
}
}
private DataTable GetActualStock()
{
try
{
SysConfig sysConfig = new SysConfig();
string StockStatisticlastTime = sysConfig.GetLastTime("StockStatisticLastTime");//最后更新时间
DateTime CreateDate = Convert.ToDateTime(StockStatisticlastTime);
long LastUpLoadStockDate = Common.LocalDateTimeToUtcLong(CreateDate);
StringBuilder sbTable = new StringBuilder();
sbTable.Append(" left join (select stock_part_id,sum(statistic_count) " +
" as statis_count from tb_parts_stock_p where statistic_Type=1 and statistic_date>" + LastUpLoadStockDate +
" group by stock_part_id ,statistic_date) as statisticResult" +
" on tb_parts_stock_p.stock_part_id=statisticResult.stock_part_id");
DataTable StockTable = DBHelper.GetTable("账面库存统计查询", "", " tb_parts_stock_p " + sbTable.ToString(),
"car_parts_code,statis_count", "", "", "");
return StockTable;
}
catch (Exception ex)
{
GlobalStaticObj_Server.GlobalLogService.WriteLog("UCHomePage", ex);
return null;
}
}
private void btnRepairBill_Click(object sender, EventArgs e)
{
try
{
#region 已结算维修单
SysConfig sysConfig = new SysConfig();
string lastTime = sysConfig.GetLastTime("RepairBillLastTime");//最后更新时间
string fileds = @"b.complete_work_time,a.create_time clearing_time,c.cust_crm_guid,b.customer_name,e.dispatch_time,b.driver_mobile,b.driver_name,b.engine_no,a.fitting_sum,
a.fitting_sum_money,b.link_man_mobile,b.linkman,b.maintain_no,h.dic_name maintain_type,a.man_hour_sum,
a.man_hour_sum_money,a.other_item_sum,a.other_item_tax_cost,a.privilege_cost,a.received_sum,
b.remark,a.should_sum,g.shut_down_time,g.start_work_time,f.sum_money,b.travel_mileage,d.dic_name vehicle_brand,vehicle_model,vehicle_no,vehicle_vin,b.maintain_id";
string table = @"tb_maintain_settlement_info a
inner join tb_maintain_info b on a.maintain_id=b.maintain_id
inner join tb_customer c on b.customer_id=c.cust_id
left join sys_dictionaries d on b.vehicle_brand=d.dic_id
left join (
select maintain_id,min(create_time) dispatch_time from tb_maintain_dispatch_worker group by maintain_id
) e on a.maintain_id=e.maintain_id
left join (
select maintain_id,sum(sum_money) sum_money from tb_maintain_other_toll group by maintain_id
) f on a.maintain_id=f.maintain_id
left join (
select maintain_id,min(start_work_time) start_work_time,MAX(shut_down_time) shut_down_time from tb_maintain_item group by maintain_id
) g on a.maintain_id=g.maintain_id
left join sys_dictionaries h on b.maintain_type=h.dic_id";
string strWhere = string.Empty;
//获取最后更新时间后的维修结算单
if (!string.IsNullOrEmpty(lastTime))
{
strWhere = string.Format("a.create_time>{0}", Common.LocalDateTimeToUtcLong(Convert.ToDateTime(lastTime)));
}
DataTable dt = DBHelper.GetTable("", GlobalStaticObj_Server.DbPrefix + GlobalStaticObj_Server.Instance.MainAccCode, table, fileds, strWhere, "", "");
if (dt == null || dt.Rows.Count == 0)
{
MessageBoxEx.Show("没有数据");
}
foreach (DataRow dr in dt.Rows)
{
Model.repairbill bill = new Model.repairbill();
bill.clearing_time = Common.UtcLongToLocalDateTime(dr["clearing_time"]);//结算时间
bill.complete_work_time = Utility.Common.Common.UtcLongToLocalDateTime(dr["complete_work_time"]);
bill.cost_types = "其他费用合计";//其他费用类别
bill.cust_id = dr["cust_crm_guid"].ToString();//客户CRM_ID
bill.cust_name = dr["customer_name"].ToString();
bill.dispatch_time = Common.UtcLongToLocalDateTime(dr["dispatch_time"]);//派工时间
bill.driver_mobile = dr["driver_mobile"].ToString();
bill.driver_name = dr["driver_name"].ToString();
bill.engine_no = dr["engine_no"].ToString();
bill.fitting_sum = dr["fitting_sum"].ToString();
bill.fitting_sum_money = dr["fitting_sum_money"].ToString();//配件货款
bill.link_man_mobile = dr["link_man_mobile"].ToString();
bill.linkman = dr["linkman"].ToString();
bill.maintain_no = dr["maintain_no"].ToString();
bill.maintain_type = dr["maintain_type"].ToString();//维修类别
bill.man_hour_sum = dr["man_hour_sum"].ToString();
bill.man_hour_sum_money = dr["man_hour_sum_money"].ToString();
bill.other_item_sum = dr["other_item_sum"].ToString();
bill.other_item_tax_cost = dr["other_item_tax_cost"].ToString();
bill.other_remarks = "";//其他费用备注
bill.privilege_cost = dr["privilege_cost"].ToString();
bill.received_sum = dr["received_sum"].ToString();
bill.remark = dr["remark"].ToString();
bill.should_sum = dr["should_sum"].ToString();
bill.shut_down_time = Common.UtcLongToLocalDateTime(dr["shut_down_time"]);//停工时间
bill.start_work_time = Common.UtcLongToLocalDateTime(dr["start_work_time"]);//开工时间
bill.sum_money = CommonCtrl.IsNullToString(dr["sum_money"]);//其他费用金额
bill.travel_mileage = dr["travel_mileage"].ToString();
bill.vehicle_brand = dr["vehicle_brand"].ToString();
bill.vehicle_model = dr["vehicle_model"].ToString();
bill.vehicle_no = dr["vehicle_no"].ToString();
bill.vehicle_vin = dr["vehicle_vin"].ToString();
#region 维修用料
string detailWhere = string.Format("maintain_id='{0}'", dr["maintain_id"]);
string detailTable = "tb_maintain_material_detail a left join tb_parts b on a.parts_code=b.ser_parts_code";
DataTable dtDetail = DBHelper.GetTable("", GlobalStaticObj_Server.DbPrefix + GlobalStaticObj_Server.Instance.MainAccCode, detailTable, "a.*,b.car_parts_code", detailWhere, "", "");
if (dtDetail == null || dtDetail.Rows.Count == 0)
{
bill.RepairmaterialDetails = new Model.RepairmaterialDetail[0];
}
else
{
int detailCount = dtDetail.Rows.Count;
bill.RepairmaterialDetails = new Model.RepairmaterialDetail[detailCount];
for (int i = 0; i < detailCount; i++)
{
DataRow drDetail = dtDetail.Rows[i];
Model.RepairmaterialDetail detail = new Model.RepairmaterialDetail();
detail.car_parts_code = drDetail["car_parts_code"].ToString();
detail.norms = drDetail["norms"].ToString();
detail.parts_name = drDetail["parts_name"].ToString();
detail.parts_remarks = drDetail["remarks"].ToString();
detail.quantity = drDetail["quantity"].ToString();
detail.sum_money = drDetail["sum_money"].ToString();
detail.three_warranty = drDetail["three_warranty"].ToString();
detail.unit = drDetail["unit"].ToString();//配件单位
detail.unit_price = drDetail["unit_price"].ToString();
detail.vehicle_brand = drDetail["vehicle_brand"].ToString();
bill.RepairmaterialDetails[i] = detail;
}
}
#endregion
#region 维修项目
string itemWhere = string.Format("maintain_id='{0}'", dr["maintain_id"]);
DataTable dtItem = DBHelper.GetTable("", GlobalStaticObj_Server.DbPrefix + GlobalStaticObj_Server.Instance.MainAccCode, "tb_maintain_item", "*", itemWhere, "", "");
if (dtItem == null || dtItem.Rows.Count == 0)
{
bill.RepairProjectDetails = new Model.RepairProjectDetail[0];
}
else
{
int itemCount = dtItem.Rows.Count;
bill.RepairProjectDetails = new Model.RepairProjectDetail[itemCount];
for (int i = 0; i < itemCount; i++)
{
DataRow drItem = dtItem.Rows[i];
Model.RepairProjectDetail item = new Model.RepairProjectDetail();
item.item_name = drItem["item_name"].ToString();
item.item_no = drItem["item_no"].ToString();
item.item_remarks = drItem["remarks"].ToString();
item.item_type = drItem["item_type"].ToString();
item.man_hour_norm_unitprice = drItem["man_hour_norm_unitprice"].ToString();
item.man_hour_quantity = drItem["man_hour_quantity"].ToString();
item.man_hour_type = drItem["man_hour_type"].ToString();
item.sum_money_goods = drItem["sum_money_goods"].ToString();
item.three_warranty = drItem["three_warranty"].ToString();
bill.RepairProjectDetails[i] = item;
}
}
#endregion
#endregion
string jsonStr = Newtonsoft.Json.JsonConvert.SerializeObject(bill);
bool flag = yuTongWebService.WebServ_YT_BusiData.UpLoadRepairBill(jsonStr);
if (!flag)
{
MessageBoxEx.Show("接口调用失败", "系统提示");
}
else
{
lastTime = Common.UtcLongToLocalDateTime(dr["clearing_time"]);
sysConfig.UpdateLastTime("RepairBillLastTime", lastTime);//更新时间
}
}
}
catch (Exception ex)
{
GlobalStaticObj_Server.GlobalLogService.WriteLog("UCHomePage", ex);
MessageBoxEx.ShowWarning("程序异常");
}
}
private DataTable GetActualStock()
{
try
{
SysConfig sysConfig = new SysConfig();
string StockStatisticlastTime = sysConfig.GetLastTime("StockLastTime");//最后更新时间
DateTime CreateDate = Convert.ToDateTime(StockStatisticlastTime);
long LastUpLoadStockDate = Common.LocalDateTimeToUtcLong(CreateDate);
string strWhere = string.Format("statistic_Type = 0 AND isnull(car_parts_code,'')<>'' and statistic_date>{0}", LastUpLoadStockDate);
DataTable StockTable = DBHelper.GetTable("账面库存统计查询", GlobalStaticObj_Server.DbPrefix + GlobalStaticObj_Server.Instance.MainAccCode,
"tb_parts_stock_p", "car_parts_code, SUM(statistic_count) AS statis_count ", strWhere, "", "GROUP BY car_parts_code");
return StockTable;
}
catch (Exception ex)
{
GlobalStaticObj_Server.GlobalLogService.WriteLog("UCHomePage", ex);
return null;
}
}